<?php
include_once 'ns_dao.class.php';

class ns_dao_software
{

    // -----------------------------------------------------------------------------------------------------------------------------------
    //  SOFTWARE
    // -----------------------------------------------------------------------------------------------------------------------------------

    public static function IncreaseDownloadCount($sid)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM stat WHERE SoftwareID='{$sid}' LIMIT 1");
        //echo "Count: ".$sql_result[0]['DownloadCount'] . '<br />';
        
        if (!isset($sql_result[0]) || empty($sql_result)) //If there are no results
        {
            $sql_result2 = $DB->query("INSERT INTO stat (SoftwareID, DownloadCount) VALUES ('{$sid}',1)");
        }
        else
        {
            $num = $sql_result[0]['DownloadCount']+1;
            $sql2 = "UPDATE stat SET DownloadCount='" . $num . "' WHERE SoftwareID='{$sid}'";
            $sql_result2 = $DB->query($sql2);
        }
        unset($DB);
    }
    
    public static function GetStatsTable()
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT SoftwareID, DownloadCount FROM stat ORDER BY DownloadCount DESC LIMIT 10");
        unset($DB);
        return $sql_result;        
    }
    
    public static function AddToUserSoftware($user, $sid)
    {
        $DB = ns_dao::create();
        //$sql_result = $DB->query("SELECT * FROM usersoftware WHERE Username='{$user}' AND SoftwareID='{$sid}' LIMIT 1");
        $sql_result2 = $DB->query("INSERT INTO usersoftware (Username, SoftwareID) VALUES ('{$user}','{$sid}')");
        //echo "INSERT INTO usersoftware (Username, SoftwareID) VALUES ('{$user}','{$sid}')";
        /*echo "Count: ".$sql_result[0]['SoftwareID'] . '<br />';
        
        if (!isset($sql_result[0]) || empty($sql_result)) //If there are no results
        {
            $sql_result2 = $DB->query("INSERT INTO usersoftware (Username, SoftwareID) VALUES ('{$user}','{$sid}')");
        }*/
        unset($DB);
    }
        
    public static function getUserSoftware($username)
    {
        $DB = ns_dao::create();
        $sql = "SELECT * FROM usersoftware WHERE username='{$username}'";
        $sql_result = $DB->query($sql);
        unset($DB);
        if (isset($sql_result)) return $sql_result;
        return false;
    }

    public static function GetSoftwareNameViaID($id)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT SoftwareName FROM software WHERE SoftwareID = {$id} limit 1");
        unset($DB);
        if (isset($sql_result)) return $sql_result[0]['SoftwareName'];
        return false;
    }
    
    public static function updateSoftwareVersion($versionID, $field, $value)
    {
        $DB = ns_dao::create();
        $rowCount = $DB->update("UPDATE softwareversion SET {$field} = '{$value}' WHERE SoftwareVersionID = '{$versionID}'");
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function CreateNewSoftwareDescription($softwareName, $softwareDescription, $category, $ownerUsername, $approved)
    {
        $DB = ns_dao::create();
        $sql = "INSERT INTO software (SoftwareName,SoftwareDescription,Category,OwnerUsername,Approved) VALUES ('{$softwareName}','{$softwareDescription}','{$category}','{$ownerUsername}','{$approved}')";
        $sql_result = $DB->DB->insert($sql);
        unset($DB);        
        return $sql_result;
    }
    
    public static function updateSoftware($softwareID, $field, $value)
    {
        $DB = ns_dao::create();
        $rowCount = $DB->update("UPDATE software SET {$field} = '{$value}' WHERE SoftwareID = '{$softwareID}'");

        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
        
    public static function updateSoftwareByArray($softwareID, $data)
    {
        $insertData = array();
        foreach ($data as $key => $value)
            if ($key != "SoftwareID")
                $insertData[$key] = (is_null($value) || trim($value) == "") ? '' : $value;

        foreach ($insertData as $key => $value)
            if (!is_numeric($value))
                $insertData[$key] = "'{$value}'";

        $sql   = "UPDATE `software` SET ";
        $blnFirst = true;
        foreach ($insertData as $key => $value){
            if ($blnFirst){
                $blnFirst = false;
                $sql = $sql . "`" . $key . "` = " . $value;
            } else {
                $sql = $sql . ",`" . $key . "` = " . $value;                
            }
        }        
        $sql = $sql . " WHERE `SoftwareID` = " . $softwareID;
        $DB = ns_dao::create();
        $rowCount = $DB->update($sql);
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function updateSoftwareVersionByArray($softwareVersionID, $data)
    {
        $insertData = array();
        foreach ($data as $key => $value)
            if ($key != "SoftwareVersionID")
                $insertData[$key] = (is_null($value) || trim($value) == "") ? '' : $value;

        foreach ($insertData as $key => $value)
            if (!is_numeric($value))
                $insertData[$key] = "'{$value}'";

        $sql   = "UPDATE `softwareVersion` SET ";
        $blnFirst = true;
        foreach ($insertData as $key => $value){
            if ($blnFirst){
                $blnFirst = false;
                $sql = $sql . "`" . $key . "` = " . $value;
            } else {
                $sql = $sql . ",`" . $key . "` = " . $value;                
            }
        }        
        $sql = $sql . " WHERE `SoftwareVersionID` = " . $softwareVersionID;
        $DB = ns_dao::create();
        $rowCount = $DB->update($sql);
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    
    
    public static function GetSoftwareVersionListByID($softwareID)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareID = '{$softwareID}' ORDER BY SoftwareVersionID DESC LIMIT 20");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareVersion::createFromArray($software_row);    

        unset($DB);
        if (isset($result)) return $result;
        return false;
    }
    
    public static function GetSoftwareList()
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);    

        unset($DB);        
        return $result;
    }
    
    public static function GetApprovedSoftwareListOfUser($username)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE Approved = 1 AND OwnerUsername = '{$username}'");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);    

        unset($DB);        
        return $result;
    }

    public static function GetUnApprovedSoftwareListOfUser($username)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE Approved != 1 AND OwnerUsername = '{$username}'");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);

        unset($DB);
        return $result;
    }
    
    public static function GetApprovedSoftwareList()
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE Approved = 1");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);    

        unset($DB);        
        return $result;
    }

    public static function GetUnApprovedSoftwareList()
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE Approved != 1");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);

        unset($DB);
        return $result;
    }

    public static function GetUserSoftwareList($username)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE SoftwareID IN (SELECT SoftwareID FROM usersoftware WHERE Username = '{$username}')");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);

        unset($DB);
        return $result;
    }

    public static function GetUserCreatedSoftwareList($username)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE OwnerUsername = '{$username}')");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);

        unset($DB);
        return $result;
    }
    
    public static function GetSoftwareDescription($softwareID)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE software.SoftwareID = {$softwareID} LIMIT 1");
        $result = SoftwareDescription::createFromArray($sql_result[0]);
        unset($DB);        
        return $result;
    }
    
//    public static function CreateSoftwareDescription(SoftwareDescription $sd)
//    {
//        $DB = ns_dao::create();
//        $count = $this->DB->InsertArray($this->DB->DB(), "software", $sd->StorableData());
//        unset($DB);
//        return ($count > 0);
//    }
       
    public static function CreateSoftwareVersion($softwareID, $version)
    {
        $date = gmdate("Y-m-d H:i:s", time());
        $DB = ns_dao::create();
        $sql = "INSERT INTO softwareversion (SoftwareID, VersionNumber, UpdateDate, Active) VALUES ('{$softwareID}','{$version}','{$date}', 0)";
        $sql_result1 = $DB->DB->insert($sql);        
        $sql2 = "SELECT * FROM softwareversion WHERE SoftwareID = {$softwareID} ORDER BY SoftwareVersionID DESC LIMIT 1";
        $sql_result2 = $DB->query($sql2);
        unset($DB);
        
        file::mkdir_safe("../../content/software/".$softwareID);
        file::mkdir_safe("../../content/software/".$softwareID."/".$sql_result2[0]["SoftwareVersionID"]);
        
        return $sql_result1;
    }    
    
    
    public static function GetCategoryList()
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM category");
        foreach ($sql_result as $key => $category_row)
            $result[$key] = Category::createFromArray($category_row);    

        unset($DB);        
        return $result;
    }
    
    public static function SoftwareByID($id)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE SoftwareID = {$id} limit 1");
        $U = SoftwareDescription::createFromArray($sql_result[0]);  
        unset($DB);
        return $U;
    }
    
    public static function SoftwareByID2($id)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE SoftwareID = {$id} limit 1");
        unset($DB);
        return $sql_result;
    }
    
    public static function SoftwareVersionLatestBySoftwareID2($id)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareID = {$id} ORDER BY SoftwareVersionID DESC LIMIT 1");
        unset($DB);
        return $sql_result;
    }
    
    public static function SoftwareVersionLatestBySoftwareID($id)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareID = {$id} ORDER BY SoftwareVersionID DESC LIMIT 1");
        $U = SoftwareVersion::createFromArray($sql_result);  
        unset($DB);
        return $U;
    }
    
    public static function SoftwareVersionLatestBySoftwareIDWithInActive($id)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareID = {$id} ORDER BY SoftwareVersionID DESC LIMIT 1");
        $U = SoftwareVersion::createFromArray($sql_result[0]);
        unset($DB);
        return $U;
    }

    public static function GetLastestSoftwareVersionBySoftwareVersionID($softwareVersionID)
    {
        $DB = ns_dao::create();
        $sql = "SELECT * FROM softwareversion WHERE SoftwareID = (SELECT SoftwareID FROM softwareversion WHERE SoftwareVersionID = {$softwareVersionID} LIMIT 1) ORDER BY SoftwareVersionID DESC LIMIT 1";
        $sql_result = $DB->query($sql);
        $U = SoftwareVersion::createFromArray($sql_result[0]);
        unset($DB);
        return $U;
    }

    public static function MakeSoftwareVersionLive($softwareVersionID)
    {
        $result = ns_dao_software::updateSoftwareVersion($softwareVersionID, "Active", "1");
        return $result;
    }
    
    public static function SoftwareVersionByVersionID($id)
    {
        $DB = ns_dao::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareVersionID = {$id}");
        $U = SoftwareVersion::createFromArray($sql_result[0]);  
        unset($DB);
        return $U;
    }
    
    public static function DeleteSoftwareVersion($id)
    {
        $DB = ns_dao::create();
        $sql = "DELETE FROM `nordic-sauna`.`softwareversion` WHERE `softwareversion`.`SoftwareVersionID` = {$id}";
        $sql_result = $DB->query($sql);
        unset($DB);
        return $sql_result;
    }
    
    public static function DeleteSoftware($id)
    {
        $DB = ns_dao::create();
        $sql = "DELETE FROM software WHERE SoftwareID={$id}";
        $sql_result = $DB->query($sql);
        unset($DB);
        return $sql_result;
    }

}

?>
